

##### Initialize
require(RPostgreSQL)


##### Get connection
getPGConn <- function(dbname, port, host, usr, pwd) {
	drv <- dbDriver("PostgreSQL")
	con <- dbConnect(drv, dbname = dbname, port=port, host=host, user=usr, password=pwd)
	return(con)
}



##### Read a table and return as data frame
dbGetTable <- function(con, schemaname, tablename) {
	t.res <- dbSendQuery(con, statement=paste("SELECT * FROM", paste(schemaname, tablename, sep="."), ";"))
	t.df <- fetch(t.res, -1)
	return(t.df)
}



##### Get a specific row from a table
dbGetRow <- function(con, schemaname, tablename, columname, wherecrit, string) {
	wherecrit <- ifelse(string, paste("'", wherecrit, "'", sep=""), wherecrit)
	t.res <- dbSendQuery(con, statement=paste("SELECT * FROM", paste(schemaname, tablename, sep="."), "WHERE", columname, "=", wherecrit, ";"))
	t.df <- fetch(t.res, -1)
	return(t.df)
}



##### Execute a given query and return result as data frame
dbGetQuery <- function(con, query) {
	t.res <- dbSendQuery(con, statement=query)
	t.df <- fetch(t.res, -1)
	return(t.df)
}



##### Return values in test vector which aren't in column of specified table
dbTestVector <- function(con, schemaname, tablename, columnname, testvec) {
	in.condition <- paste("(", paste(paste("'", testvec, "'", sep=""), collapse=","), ")")
	t.res <- dbSendQuery(con, statement=paste("SELECT", columnname, "FROM", paste(schemaname, tablename, sep="."), "WHERE", columnname, "IN", in.condition, ";"))
	t.df <- fetch(t.res, -1)
	if (ncol(t.df) > 0) {
		resultvec <- testvec[!(testvec %in% t.df[,1])]
	} else {
		resultvec <- testvec
	}
	return(resultvec)
}



##### Insert a row into a table
dbInsert <- function(con, schemaname, tablename, columns, values, string) {
	columns <- paste("(", paste(columns, collapse=","), ")")
	values.adj <- rep(NA, length(values))
	for (i in 1:length(values)) {
		if (string[i]) {
			values.adj[i] <- paste("'", values[i], "'", sep="")
		} else {
			values.adj[i] <- values[i]
		}
	}
	values.adj <- paste("(", paste(values.adj, collapse=","), ")")
	dbSendQuery(con, statement=paste("INSERT INTO", paste(schemaname, tablename, sep="."), columns, "VALUES", values.adj, ";"))
}



##### Insert data frame into table on DB
dbInsertDF <- function(con, schemaname, tablename, input.df, columns=NULL) {
	string <- rep(NA, ncol(input.df))
	for (c in 1:ncol(input.df)) {
		if (is.numeric(input.df[,c])) {
			string[c] <- FALSE
		} else {
			string[c] <- TRUE
		}
	}
	if (is.null(columns)) {
		columns <- colnames(input.df)
	}
	columns <- paste("(", paste(columns, collapse=","), ")")
	for (r in 1:nrow(input.df)) {
		values.char <- paste(input.df[r,1])
		values.char <- ifelse(string[1], paste("'", values.char, "'", sep=""), values.char)
		if (ncol(input.df) > 1) {
			for (v in 2:ncol(input.df)) {
				if (string[v]) {
					values.char <- paste(values.char, ", '", input.df[r,v], "'", sep="")
				} else {
					values.char <- paste(values.char, ", ", input.df[r,v], sep="")
				}
			}
		}
		values.char <- paste("(", values.char, ")", sep="")
		values.char <- iconv(values.char, from = "latin1", to = "UTF8")
		dbSendQuery(con, statement=paste("INSERT INTO", paste(schemaname, tablename, sep="."), columns, "VALUES", values.char, ";"))
	}
}




##### Create table on DB
dbCreateTable <- function(con, schemaname, tablename, colnames, coltypes, drop=FALSE) {
		if(drop) {
			query <- paste("DROP TABLE IF EXISTS ", schemaname, ".", tablename, ";", sep="")
			dbSendQuery(con, statement=query)
		}
		
		query <- paste("CREATE TABLE ", schemaname, ".", tablename, " (", sep="")
		for (i in 1:length(colnames)) {
			query <- paste(query, " ", colnames[i], " ", coltypes[i], " ", sep="")
			if (i < length(colnames)) {
				query <- paste(query, ", ", sep="")
			}
		}
		query <- paste(query, ")", sep="")
		
		retval <- dbSendQuery(con, statement=query)
}





##### Execute SQL script on DB
dbRunScript <- function(con, path, return=FALSE, script.param=vector("list", 0), param.quote=NULL) {
  options(scipen=999)
  script.sql <- readChar(path, file.info(path)$size)
	script.sql <- gsub("\n", " \n ", script.sql, fixed = TRUE)
	script.sql <- gsub("\t", " ", script.sql, fixed = TRUE)
	script.sql <- gsub("﻿", "", script.sql, fixed = TRUE)
	if (length(script.param) > 0) {
		if (is.null(param.quote)) {
			param.quote <- rep(FALSE, length(script.param))
		} else {
			if (length(param.quote) != length(script.param)) {
				stop("'param.quote' and 'script.param' are of unequal length.")
			}
		}
		for (i in 1:length(script.param)) {
			param.name <- names(script.param)[i]
			param.value <- script.param[[i]]
			param.value <- ifelse(param.quote[i], paste("'", param.value, "'", sep=""), param.value)
			script.sql <- gsub(param.name, param.value, script.sql, fixed = TRUE)
		}
	}
	rs <- dbSendQuery(con, statement=script.sql)
	if(return) {
		rs.df <- fetch(rs, -1)
		return(rs.df)
	}
  options(scipen=0)
} 


